Grouping Records Using GROUP BY in MySQL

Course: MySQL Training

Topic: Grouping Records Using GROUP BY

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

---

1. Introduction to GROUP BY in MySQL

In MySQL, the GROUP BY clause is used to group rows that have the same values in one or more columns. It is commonly used with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() to summarize data and generate meaningful reports.

For example, a company may store hundreds or thousands of sales transactions in a database. Instead of displaying every transaction individually, the company may want to calculate total sales for each product category, count employees in each department, or find the average salary in each department. The GROUP BY clause makes these calculations possible.

Grouping records does not permanently combine or delete rows in the table. It creates summarized results for the query being executed. The original records remain unchanged.

Example

Consider a table named sales containing the following records:

sale_id	product_name	category	amount
1	Laptop	Electronics	50000
2	Mouse	Accessories	800
3	Keyboard	Accessories	1500
4	Monitor	Electronics	12000
5	Chair	Furniture	7500

To calculate the total sales amount for each category, use:

SELECT
    category,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category;

Expected output:

category	total_sales
Accessories	2300
Electronics	62000
Furniture	7500

The query groups records by the category column and calculates the total amount for each group. The order of the result rows is not guaranteed unless you use ORDER BY.

2. Learning Objectives

After completing this topic, you will be able to:

• Understand the purpose of the GROUP BY clause in MySQL.
• Group records based on one or more columns.
• Use aggregate functions with grouped data.
• Count records in each group using COUNT().
• Calculate totals using SUM().
• Calculate averages using AVG().
• Find minimum and maximum values for each group.
• Filter records before grouping with WHERE.
• Filter grouped results using HAVING.
• Sort grouped results using ORDER BY.
• Group data by multiple columns and date-based expressions.
• Understand common GROUP BY errors and how to correct them.
• Create summary reports using real-world database records.

3. What Is GROUP BY?

The GROUP BY clause organizes rows into groups based on the values of specified columns or expressions. Rows with the same grouping values belong to the same group.

When used with aggregate functions, MySQL calculates one summarized result for each group.

3.1 Basic Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

Explanation:

• SELECT specifies the columns and calculations to display.
• column_name identifies the column used to group records.
• aggregate_function() performs a calculation on the records in each group.
• FROM specifies the source table.
• GROUP BY creates groups based on matching values.

3.2 How GROUP BY Works

1. MySQL identifies the rows selected from the source table.
2. The WHERE clause, if present, filters individual rows.
3. MySQL groups the remaining rows using the specified grouping columns.
4. Aggregate functions calculate results for each group.
5. The HAVING clause, if present, filters the groups.
6. The ORDER BY clause, if present, sorts the results.
7. The LIMIT clause, if present, restricts the number of returned rows.

3.3 Why Is GROUP BY Important?

• It summarizes large datasets into manageable results.
• It helps compare categories, departments, products, and customers.
• It supports business intelligence and reporting.
• It works with aggregate functions to calculate meaningful statistics.
• It reduces the need to calculate totals manually outside the database.
• It helps identify trends and patterns in stored data.

4. Creating a Sample Database and Table

To understand GROUP BY practically, create a database and a sample sales table.

4.1 Create a Database

CREATE DATABASE company_db;

This statement creates a database named company_db. If the database already exists, use an appropriate existence check before creating it again.

4.2 Select the Database

USE company_db;

The USE statement selects the database for subsequent SQL operations.

4.3 Create the Sales Table

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    quantity INT,
    amount DECIMAL(12, 2),
    sale_date DATE,
    payment_status VARCHAR(20)
);

The table stores sales transactions, including product names, categories, quantities, amounts, dates, and payment statuses.

4.4 Insert Sample Records

INSERT INTO sales
(sale_id, product_name, category, quantity, amount, sale_date, payment_status)
VALUES
(1, 'Laptop', 'Electronics', 2, 100000.00, '2026-01-05', 'Paid'),
(2, 'Keyboard', 'Accessories', 5, 7500.00, '2026-01-08', 'Paid'),
(3, 'Mouse', 'Accessories', 10, 8000.00, '2026-01-12', 'Pending'),
(4, 'Monitor', 'Electronics', 3, 36000.00, '2026-02-02', 'Paid'),
(5, 'Printer', 'Electronics', 2, 24000.00, '2026-02-10', 'Pending'),
(6, 'Desk', 'Furniture', 4, 32000.00, '2026-02-15', 'Paid'),
(7, 'Chair', 'Furniture', 6, 18000.00, '2026-03-01', 'Paid'),
(8, 'Webcam', 'Accessories', 4, 12000.00, '2026-03-06', 'Pending');

4.5 View the Records

SELECT *
FROM sales;

Viewing the records helps you understand how individual rows will be combined into groups in subsequent queries.

5. Grouping Records by a Single Column

The simplest use of GROUP BY is grouping records using one column. For example, you can group sales by category, employees by department, or orders by payment status.

5.1 Display Each Unique Category

SELECT category
FROM sales
GROUP BY category;

This query returns one row for each distinct category represented in the table. It does not calculate totals because no aggregate function is included.

For simply retrieving unique values, SELECT DISTINCT category FROM sales; is also appropriate.

5.2 Count Records in Each Category

SELECT
    category,
    COUNT(*) AS total_records
FROM sales
GROUP BY category;

Expected output:

category	total_records
Accessories	3
Electronics	3
Furniture	2

The query counts the sales records in each category. COUNT(*) counts rows, including rows that contain NULL values in other columns.

5.3 Group Sales by Payment Status

SELECT
    payment_status,
    COUNT(*) AS transaction_count
FROM sales
GROUP BY payment_status;

This query returns the number of transactions marked as Paid and Pending.

6. Using GROUP BY with COUNT()

The COUNT() aggregate function counts rows or non-NULL values. When combined with GROUP BY, it calculates a separate count for each group.

6.1 Count Sales by Category

SELECT
    category,
    COUNT(*) AS number_of_sales
FROM sales
GROUP BY category;

This query displays how many sales records belong to each category.

6.2 Count Distinct Products by Category

SELECT
    category,
    COUNT(DISTINCT product_name) AS distinct_products
FROM sales
GROUP BY category;

COUNT(DISTINCT product_name) counts unique, non-NULL product names within each category. This is useful when the same product may appear in multiple transactions.

6.3 Count Transactions by Payment Status

SELECT
    payment_status,
    COUNT(*) AS total_transactions
FROM sales
GROUP BY payment_status;

This query helps compare the number of paid transactions with the number of pending transactions.

7. Using GROUP BY with SUM()

The SUM() function calculates the total of numeric values. When combined with GROUP BY, it calculates a separate total for each group.

7.1 Calculate Total Sales by Category

SELECT
    category,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category;

Expected output:

category	total_sales
Accessories	27500.00
Electronics	160000.00
Furniture	50000.00

MySQL adds the amounts within each category and returns one total per group.

7.2 Calculate Total Quantity by Category

SELECT
    category,
    SUM(quantity) AS total_quantity
FROM sales
GROUP BY category;

This query calculates the total quantity recorded for each category.

7.3 Calculate Sales by Payment Status

SELECT
    payment_status,
    SUM(amount) AS total_amount
FROM sales
GROUP BY payment_status;

This query shows the total recorded amount for Paid and Pending transactions separately.

8. Using GROUP BY with AVG(), MIN(), and MAX()

MySQL aggregate functions can be combined with GROUP BY to calculate different statistics for each group.

8.1 Calculate Average Sales by Category

SELECT
    category,
    AVG(amount) AS average_sales
FROM sales
GROUP BY category;

The AVG() function calculates the average of non-NULL amount values in each category.

8.2 Find the Minimum Sale Amount by Category

SELECT
    category,
    MIN(amount) AS minimum_sale
FROM sales
GROUP BY category;

This query returns the smallest recorded sale amount for each category.

8.3 Find the Maximum Sale Amount by Category

SELECT
    category,
    MAX(amount) AS maximum_sale
FROM sales
GROUP BY category;

This query returns the largest recorded sale amount for each category.

8.4 Calculate Multiple Statistics Together

SELECT
    category,
    COUNT(*) AS transaction_count,
    SUM(amount) AS total_sales,
    AVG(amount) AS average_sales,
    MIN(amount) AS minimum_sale,
    MAX(amount) AS maximum_sale
FROM sales
GROUP BY category;

This query generates a detailed summary of each category in one result set.

9. Grouping Records by Multiple Columns

MySQL allows you to group records using more than one column. When multiple columns are specified, each unique combination of their values creates a separate group.

9.1 Group by Category and Payment Status

SELECT
    category,
    payment_status,
    COUNT(*) AS transaction_count,
    SUM(amount) AS total_amount
FROM sales
GROUP BY category, payment_status;

For example, Electronics/Paid and Electronics/Pending are treated as separate groups.

9.2 Group by Category and Product

SELECT
    category,
    product_name,
    SUM(quantity) AS total_quantity,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category, product_name;

This query summarizes the quantity and sales amount for each category and product combination.

9.3 Group by Year and Category

SELECT
    YEAR(sale_date) AS sales_year,
    category,
    SUM(amount) AS total_sales
FROM sales
GROUP BY YEAR(sale_date), category
ORDER BY sales_year, category;

This query calculates the total sales for each category within each year.

9.4 Understanding Multiple-Column Grouping

• The first grouping column divides records into larger groups.
• The next grouping column divides those groups into smaller groups.
• Each unique combination of grouping values produces a separate result row.
• Adding more grouping columns generally increases the number of possible groups.
• Use only the grouping columns needed for the analysis.

10. Using GROUP BY with WHERE

The WHERE clause filters individual rows before the grouping operation. This allows you to calculate summaries using only records that satisfy specific conditions.

10.1 Group Electronics Sales Only

SELECT
    category,
    SUM(amount) AS total_sales
FROM sales
WHERE category = 'Electronics'
GROUP BY category;

This query includes only Electronics records before calculating the category total.

10.2 Group Paid Transactions Only

SELECT
    category,
    COUNT(*) AS paid_transactions,
    SUM(amount) AS paid_sales
FROM sales
WHERE payment_status = 'Paid'
GROUP BY category;

The query first filters the records to include only paid transactions and then summarizes them by category.

10.3 Group Sales Within a Date Range

SELECT
    category,
    SUM(amount) AS period_sales
FROM sales
WHERE sale_date >= '2026-02-01'
  AND sale_date < '2026-03-01'
GROUP BY category;

This query calculates category totals for February 2026. The inclusive start and exclusive end date pattern is also useful when working with date-time columns.

10.4 Combine Multiple WHERE Conditions

SELECT
    category,
    SUM(amount) AS total_sales
FROM sales
WHERE payment_status = 'Paid'
  AND sale_date >= '2026-02-01'
GROUP BY category;

This query groups only paid transactions dated February 1, 2026, or later.

11. Using GROUP BY with HAVING

The HAVING clause filters groups after aggregate calculations have been performed. It is especially useful when you want to display only groups that meet a condition based on their count, total, average, minimum, or maximum.

11.1 Categories with Total Sales Above 30000

SELECT
    category,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category
HAVING SUM(amount) > 30000;

This query returns only categories whose total sales exceed 30000.

11.2 Categories with More Than Two Transactions

SELECT
    category,
    COUNT(*) AS transaction_count
FROM sales
GROUP BY category
HAVING COUNT(*) > 2;

This query displays categories containing more than two sales records.

11.3 Categories with an Average Sale Above 10000

SELECT
    category,
    AVG(amount) AS average_sales
FROM sales
GROUP BY category
HAVING AVG(amount) > 10000;

This query returns categories whose average recorded sale amount is greater than 10000.

11.4 Using WHERE and HAVING Together

SELECT
    category,
    SUM(amount) AS paid_sales
FROM sales
WHERE payment_status = 'Paid'
GROUP BY category
HAVING SUM(amount) > 20000;

First, MySQL selects paid transactions. It then groups the selected rows by category and calculates the total for each group. Finally, HAVING returns only groups whose paid sales exceed 20000.

11.5 Difference Between WHERE and HAVING

WHERE	HAVING
Filters individual rows before grouping.	Filters groups after aggregation.
Commonly uses regular column conditions.	Commonly uses aggregate conditions.
WHERE payment_status = 'Paid'	HAVING SUM(amount) > 20000
Can reduce the number of rows that need to be grouped.	Determines which completed groups appear in the result.

12. Sorting Grouped Results with ORDER BY

The ORDER BY clause sorts the grouped results. By default, result ordering should not be assumed when no explicit ordering is specified.

12.1 Sort Categories by Total Sales

SELECT
    category,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category
ORDER BY total_sales DESC;

The DESC keyword sorts the category totals from highest to lowest.

12.2 Sort Categories Alphabetically

SELECT
    category,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category
ORDER BY category ASC;

The ASC keyword sorts the category names in ascending order.

12.3 Display the Top Two Categories

SELECT
    category,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category
ORDER BY total_sales DESC
LIMIT 2;

This query returns the two categories with the highest total sales.

12.4 Sort by Multiple Columns

SELECT
    category,
    payment_status,
    SUM(amount) AS total_amount
FROM sales
GROUP BY category, payment_status
ORDER BY category ASC, total_amount DESC;

This query sorts first by category and then by total amount within each category.

13. Grouping Records by Date

Grouping by date is useful for creating daily, monthly, quarterly, or yearly reports. MySQL date functions can extract the required date components for grouping.

13.1 Group Sales by Day

SELECT
    sale_date,
    SUM(amount) AS daily_sales
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

This query returns one summary row for each sale date.

13.2 Group Sales by Month

SELECT
    YEAR(sale_date) AS sales_year,
    MONTH(sale_date) AS sales_month,
    SUM(amount) AS monthly_sales
FROM sales
GROUP BY YEAR(sale_date), MONTH(sale_date)
ORDER BY sales_year, sales_month;

Grouping by both year and month keeps the same month from different years separate.

13.3 Group Sales by Year

SELECT
    YEAR(sale_date) AS sales_year,
    SUM(amount) AS yearly_sales
FROM sales
GROUP BY YEAR(sale_date)
ORDER BY sales_year;

This query calculates total sales for each year.

13.4 Group Sales by Weekday

SELECT
    DAYNAME(sale_date) AS weekday_name,
    COUNT(*) AS transaction_count,
    SUM(amount) AS total_sales
FROM sales
GROUP BY DAYOFWEEK(sale_date), DAYNAME(sale_date)
ORDER BY DAYOFWEEK(sale_date);

This query summarizes sales by weekday name. The ordering uses the weekday number so the result follows MySQL's Sunday-to-Saturday numbering rather than alphabetical order.

14. Grouping Records by Expressions

MySQL allows grouping by expressions as well as ordinary column names. This is useful when categories need to be calculated from existing values.

14.1 Group Products by Price Range

SELECT
    CASE
        WHEN amount < 10000 THEN 'Below 10000'
        WHEN amount < 50000 THEN '10000 to 49999.99'
        ELSE '50000 and above'
    END AS sales_range,
    COUNT(*) AS transaction_count,
    SUM(amount) AS total_sales
FROM sales
GROUP BY
    CASE
        WHEN amount < 10000 THEN 'Below 10000'
        WHEN amount < 50000 THEN '10000 to 49999.99'
        ELSE '50000 and above'
    END;

The CASE expression assigns each record to a price range, and GROUP BY summarizes records belonging to each range.

14.2 Group by the First Letter of a Product Name

SELECT
    LEFT(product_name, 1) AS first_letter,
    COUNT(*) AS product_count
FROM sales
GROUP BY LEFT(product_name, 1)
ORDER BY first_letter;

This query groups records according to the first character of the product name. It can be useful for basic text-based analysis.

14.3 Use an Expression Alias

SELECT
    YEAR(sale_date) AS sales_year,
    SUM(amount) AS yearly_sales
FROM sales
GROUP BY sales_year
ORDER BY sales_year;

MySQL permits the use of a SELECT alias in the GROUP BY clause. Using a clear alias can make a query easier to read.

15. Grouping Records with NULL Values

NULL represents a missing or unknown value in MySQL. When a grouping column contains NULL values, rows with NULL in that column are placed into the same group.

15.1 Example with NULL Categories

CREATE TABLE category_test (
    record_id INT PRIMARY KEY,
    category VARCHAR(50),
    amount DECIMAL(10, 2)
);

INSERT INTO category_test (record_id, category, amount)
VALUES
(1, 'Electronics', 1000.00),
(2, 'Furniture', 2000.00),
(3, NULL, 500.00),
(4, NULL, 700.00);

SELECT
    category,
    COUNT(*) AS record_count,
    SUM(amount) AS total_amount
FROM category_test
GROUP BY category;

The output contains a NULL category group with two records and a total amount of 1200.00.

15.2 Exclude NULL Categories

SELECT
    category,
    SUM(amount) AS total_amount
FROM category_test
WHERE category IS NOT NULL
GROUP BY category;

The WHERE condition removes rows with missing categories before grouping.

15.3 Display a Label for NULL Categories

SELECT
    COALESCE(category, 'Uncategorized') AS category_name,
    COUNT(*) AS record_count,
    SUM(amount) AS total_amount
FROM category_test
GROUP BY category;

This query displays a more readable label for missing categories. Because the grouping still uses the original category column, all NULL category rows remain together in one group.

16. Understanding GROUP BY and DISTINCT

Both GROUP BY and DISTINCT can return unique values, but they are used for different purposes.

GROUP BY	DISTINCT
Groups rows by specified columns or expressions.	Removes duplicate rows from the selected result.
Commonly used with aggregate functions.	Commonly used to retrieve unique values.
Can calculate totals, counts, and averages per group.	Does not calculate group totals by itself.
SELECT category, COUNT(*) FROM sales GROUP BY category;	SELECT DISTINCT category FROM sales;

16.1 When Should You Use GROUP BY?

Use GROUP BY when you need to summarize or calculate values for each group, such as total sales per category or employee count per department.

16.2 When Should You Use DISTINCT?

Use DISTINCT when you only need unique values and do not need aggregate calculations for each group.

17. GROUP BY and Aggregate Functions

Aggregate functions summarize values within each group. The following table describes common combinations.

Function	Purpose with GROUP BY	Example
COUNT(*)	Counts rows in each group.	COUNT(*) AS total_records
SUM(amount)	Adds numeric values in each group.	SUM(amount) AS total_sales
AVG(amount)	Calculates the average of non-NULL values.	AVG(amount) AS average_sales
MIN(amount)	Finds the smallest non-NULL value.	MIN(amount) AS minimum_sale
MAX(amount)	Finds the largest non-NULL value.	MAX(amount) AS maximum_sale

17.1 Example of Multiple Aggregate Functions

SELECT
    payment_status,
    COUNT(*) AS transaction_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS average_amount,
    MIN(amount) AS minimum_amount,
    MAX(amount) AS maximum_amount
FROM sales
GROUP BY payment_status;

This query provides a complete statistical summary for each payment status.

18. GROUP BY and ONLY_FULL_GROUP_BY

MySQL commonly uses the ONLY_FULL_GROUP_BY SQL mode to enforce correct grouping rules. When this mode is enabled, nonaggregated columns in the SELECT list generally must appear in the GROUP BY clause or be functionally dependent on the grouping columns.

18.1 Incorrect Query

SELECT
    category,
    product_name,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category;

This query may produce an error because several product names can belong to one category, and product_name is neither grouped nor aggregated.

18.2 Correct Query: Group by Both Columns

SELECT
    category,
    product_name,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category, product_name;

This version returns a separate result for each category and product combination.

18.3 Correct Query: Return Only the Category Summary

SELECT
    category,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category;

This version removes the product name because the intended output contains only one total per category.

18.4 Why Should You Follow These Rules?

• They prevent ambiguous values from appearing in grouped results.
• They make queries more portable and predictable.
• They help ensure that each output row represents a well-defined group.
• They reduce the risk of accidentally reporting an arbitrary product name or other nonaggregated value.

19. Common GROUP BY Errors and Troubleshooting

19.1 Forgetting the GROUP BY Clause

Problem: A query selects a category along with an aggregate function but does not group by the category.

SELECT category, SUM(amount)
FROM sales;

With ONLY_FULL_GROUP_BY enabled, this query is invalid because the category is not aggregated or grouped.

Solution:

SELECT category, SUM(amount) AS total_sales
FROM sales
GROUP BY category;

19.2 Selecting an Unrelated Column

Problem: A query groups by category but also selects a product name that can vary within that category.

Solution: Include the product name in the grouping when you need a separate row for each product, or remove it when you need only category totals.

19.3 Using WHERE Instead of HAVING

Problem: A query tries to filter on an aggregate calculation using WHERE.

SELECT category, SUM(amount) AS total_sales
FROM sales
WHERE SUM(amount) > 30000
GROUP BY category;

Solution: Use HAVING for conditions involving aggregate results.

SELECT category, SUM(amount) AS total_sales
FROM sales
GROUP BY category
HAVING SUM(amount) > 30000;

19.4 Getting Unexpected Counts

Problem: The query returns more records than expected for each group.

Possible causes:

• The source table contains repeated transactions.
• A JOIN multiplies rows because of one-to-many relationships.
• The query counts rows when it should count distinct entities.

Solution: Inspect the underlying records and determine whether COUNT(*) or COUNT(DISTINCT column_name) matches the intended calculation.

19.5 Unexpected Grouping of Text Values

Problem: Text values that look different are grouped together, or values that appear similar are treated separately.

Solution: Check the column's character set and collation. Differences in letter case, accents, spaces, or formatting can affect comparisons and grouping.

19.6 Unexpected Result Ordering

Problem: Grouped results appear in an unexpected order.

Solution: Specify an explicit ORDER BY clause.

SELECT category, SUM(amount) AS total_sales
FROM sales
GROUP BY category
ORDER BY total_sales DESC;

19.7 Grouping by the Wrong Column

Problem: The query produces more groups or fewer groups than expected.

Solution: Verify the grouping columns and make sure they represent the business question. Grouping by category creates category-level totals, while grouping by category and product creates more detailed summaries.

20. Best Practices for Using GROUP BY

• Group by the columns that define the summaries you need.
• Use aggregate functions when calculating totals, counts, averages, minimums, or maximums.
• Keep nonaggregated SELECT columns compatible with the GROUP BY clause.
• Use descriptive aliases such as total_sales, transaction_count, and average_amount.
• Use WHERE to filter individual rows before grouping.
• Use HAVING to filter groups based on aggregate values.
• Use ORDER BY when a specific result order is required.
• Use DISTINCT inside aggregate functions only when unique values are specifically required.
• Be careful when grouping records after joining multiple tables because joins can duplicate rows.
• Use appropriate data types for dates, numeric amounts, and category fields.
• Handle NULL values deliberately when grouping and summarizing data.
• Use date expressions consistently when producing monthly or yearly summaries.
• Check query performance on large tables and consider indexes that support filtering and grouping.
• Validate results against the source data when troubleshooting unexpected totals.

21. Practical Exercises

Use the sales table created earlier to practise the following tasks.

Exercise 1: Display Unique Categories

Write a query that displays each category once using GROUP BY.

Exercise 2: Count Transactions by Category

Calculate the number of sales records in each category.

Exercise 3: Calculate Total Sales by Category

Display each category and its total recorded sales amount.

Exercise 4: Calculate Average Sale Amount

Calculate the average amount for each category.

Exercise 5: Group by Payment Status

Display the number of transactions and total amount for each payment status.

Exercise 6: Group by Category and Payment Status

Calculate the total amount for each category and payment-status combination.

Exercise 7: Filter Categories by Total Sales

Display categories whose total sales exceed 30000.

Exercise 8: Sort Categories by Total Sales

Display category totals from highest to lowest.

Exercise 9: Calculate Monthly Sales

Group the records by year and month and calculate the total sales amount for each month.

Exercise 10: Find Categories with More Than Two Transactions

Use HAVING to return categories containing more than two sales records.

22. Practice Solutions

Solution 1: Display Unique Categories

SELECT category
FROM sales
GROUP BY category;

Solution 2: Count Transactions by Category

SELECT
    category,
    COUNT(*) AS transaction_count
FROM sales
GROUP BY category;

Solution 3: Total Sales by Category

SELECT
    category,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category;

Solution 4: Average Sale Amount

SELECT
    category,
    AVG(amount) AS average_sales
FROM sales
GROUP BY category;

Solution 5: Group by Payment Status

SELECT
    payment_status,
    COUNT(*) AS transaction_count,
    SUM(amount) AS total_amount
FROM sales
GROUP BY payment_status;

Solution 6: Category and Payment Status

SELECT
    category,
    payment_status,
    SUM(amount) AS total_amount
FROM sales
GROUP BY category, payment_status;

Solution 7: Categories Above 30000

SELECT
    category,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category
HAVING SUM(amount) > 30000;

Solution 8: Sort by Total Sales

SELECT
    category,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category
ORDER BY total_sales DESC;

Solution 9: Monthly Sales

SELECT
    YEAR(sale_date) AS sales_year,
    MONTH(sale_date) AS sales_month,
    SUM(amount) AS monthly_sales
FROM sales
GROUP BY YEAR(sale_date), MONTH(sale_date)
ORDER BY sales_year, sales_month;

Solution 10: Categories with More Than Two Transactions

SELECT
    category,
    COUNT(*) AS transaction_count
FROM sales
GROUP BY category
HAVING COUNT(*) > 2;

23. Mini Project: Sales Summary Report Using GROUP BY

Project objective: Create a sales summary report that groups transaction records into meaningful categories and calculates useful business statistics.

Project Requirements

• Calculate total sales for each product category.
• Count transactions in each category.
• Calculate average, minimum, and maximum sale amounts.
• Compare total amounts by payment status.
• Summarize sales by category and payment status.
• Calculate monthly sales totals.
• Identify categories whose total sales exceed a chosen threshold.
• Sort the category summaries from highest to lowest sales.

Project Query 1: Category Summary

SELECT
    category,
    COUNT(*) AS transaction_count,
    SUM(amount) AS total_sales,
    AVG(amount) AS average_sales,
    MIN(amount) AS minimum_sale,
    MAX(amount) AS maximum_sale
FROM sales
GROUP BY category
ORDER BY total_sales DESC;

Project Query 2: Payment Summary

SELECT
    payment_status,
    COUNT(*) AS transaction_count,
    SUM(amount) AS total_amount
FROM sales
GROUP BY payment_status
ORDER BY total_amount DESC;

Project Query 3: Category and Payment Summary

SELECT
    category,
    payment_status,
    SUM(amount) AS total_amount,
    COUNT(*) AS transaction_count
FROM sales
GROUP BY category, payment_status
ORDER BY category, total_amount DESC;

Project Query 4: Monthly Sales Summary

SELECT
    YEAR(sale_date) AS sales_year,
    MONTH(sale_date) AS sales_month,
    SUM(amount) AS monthly_sales
FROM sales
GROUP BY YEAR(sale_date), MONTH(sale_date)
ORDER BY sales_year, sales_month;

Project Query 5: High-Performing Categories

SELECT
    category,
    SUM(amount) AS total_sales
FROM sales
GROUP BY category
HAVING SUM(amount) > 30000
ORDER BY total_sales DESC;

Expected Learning Outcome

After completing this project, you should be able to transform individual transaction records into grouped summaries that help businesses compare categories, review payment statuses, and analyze sales performance.

24. Interview Questions and Answers

Q1. What is GROUP BY in MySQL?

Answer: GROUP BY is a SQL clause that groups rows with matching values in specified columns or expressions. It is often used with aggregate functions to summarize each group.

Q2. Why is GROUP BY used?

Answer: It is used to summarize data, such as calculating total sales by category, employee counts by department, or average amounts by payment status.

Q3. Can GROUP BY be used without aggregate functions?

Answer: Yes. It can return one row for each distinct combination of grouping values. However, DISTINCT is often simpler when the goal is only to retrieve unique values.

Q4. Can multiple columns be used in GROUP BY?

Answer: Yes. Multiple columns can be specified, and each unique combination of their values creates a separate group.

Q5. What is the difference between GROUP BY and ORDER BY?

Answer: GROUP BY organizes records into groups for summarization, while ORDER BY sorts the resulting rows.

Q6. What is the difference between WHERE and HAVING?

Answer: WHERE filters individual rows before grouping, whereas HAVING filters groups after aggregate calculations.

Q7. Which aggregate functions are commonly used with GROUP BY?

Answer: Common aggregate functions include COUNT(), SUM(), AVG(), MIN(), and MAX().

Q8. What happens to NULL values in a GROUP BY column?

Answer: Rows with NULL in the grouping column are grouped together into one NULL group. Aggregate functions applied to other columns handle their NULL values according to their own rules.

Q9. Why can a GROUP BY query produce an error with ONLY_FULL_GROUP_BY?

Answer: MySQL may reject a query when a selected nonaggregated column is neither included in the grouping columns nor functionally dependent on them.

Q10. Can GROUP BY be used with date functions?

Answer: Yes. Functions such as YEAR() and MONTH() can be used to group records into yearly or monthly summaries.

Q11. How do you filter groups with a total greater than 50000?

Answer: Use HAVING with the aggregate expression.

SELECT category, SUM(amount) AS total_sales
FROM sales
GROUP BY category
HAVING SUM(amount) > 50000;

Q12. Does GROUP BY guarantee the order of the output?

Answer: No. Use an explicit ORDER BY clause when the result must follow a particular order.

25. Quick Revision Checklist

• GROUP BY groups rows with matching values.
• It is commonly used with aggregate functions.
• COUNT() counts records or non-NULL values.
• SUM() calculates totals within each group.
• AVG() calculates the average of non-NULL values.
• MIN() and MAX() return the smallest and largest non-NULL values.
• Multiple grouping columns create groups based on unique combinations.
• WHERE filters rows before grouping.
• HAVING filters groups after aggregation.
• ORDER BY sorts the results.
• DISTINCT is useful when only unique values are needed.
• NULL values in a grouping column form a separate group.
• Use appropriate grouping columns to avoid incorrect summaries.
• Follow ONLY_FULL_GROUP_BY rules to produce reliable queries.
• Use date functions to create daily, monthly, and yearly summaries.

26. Conclusion

The GROUP BY clause is an essential MySQL feature for organizing records into groups and generating summarized results. It allows developers and analysts to calculate totals, counts, averages, minimum values, and maximum values for categories, products, customers, payment statuses, and date periods.

By combining GROUP BY with aggregate functions, WHERE, HAVING, and ORDER BY, you can create flexible and meaningful reports from large datasets. Understanding grouping rules, NULL values, multiple-column grouping, and aggregate filtering helps you write accurate and maintainable SQL queries.

Practise the examples and exercises in this lesson to build confidence in using GROUP BY for real-world MySQL database operations.

Learn More: Explore the MySQL Training Course and register for a course demo to continue learning SQL and database concepts.